package org.insightech.er.db.impl.postgres;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.insightech.er.editor.model.dbimport.ImportFromDBManagerEclipseBase;

public class PostgresTableImportManager extends ImportFromDBManagerEclipseBase {

    @Override
    protected String getTableNameWithSchema(final String schema, final String tableName) {
        return dbSetting.getTableNameWithSchema("\"" + tableName + "\"", schema);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    protected String getViewDefinitionSQL(final String schema) {
        if (schema != null) {
            return "SELECT definition FROM pg_views WHERE schemaname = ? and viewname = ? ";

        } else {
            return "SELECT definition FROM pg_views WHERE viewname = ? ";

        }
    }

    @Override
    protected ColumnData createColumnData(final ResultSet columnSet) throws SQLException {
        final ColumnData columnData = super.createColumnData(columnSet);
        final String type = columnData.type.toLowerCase();

        if (type.startsWith("time")) {
            columnData.size = columnData.decimalDegits;
        }

        return columnData;
    }

    @Override
    protected void cacheOtherColumnData(final String tableName, final String schema, final ColumnData columnData) throws SQLException {

        if (columnData.type.equals("interval")) {
            final String restrictType = getRestrictType(tableName, schema, columnData);

            if (restrictType != null && restrictType.indexOf("(") != -1) {
                columnData.size = columnData.decimalDegits;

            } else {
                columnData.size = 0;

            }

            columnData.type = restrictType;
            columnData.decimalDegits = 0;
        }
    }

    private String getRestrictType(final String tableName, final String schema, final ColumnData columnData) throws SQLException {
        String type = null;

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = con.prepareStatement("select atttypmod from pg_attribute" + " inner join pg_stat_user_tables " + " on pg_stat_user_tables.relid = pg_attribute.attrelid " + " where pg_stat_user_tables.relname = ? " + " and pg_stat_user_tables.schemaname=? " + " and pg_attribute.attname = ?");

            ps.setString(1, tableName);
            ps.setString(2, schema);
            ps.setString(3, columnData.columnName);

            rs = ps.executeQuery();

            if (rs.next()) {
                final int atttypmod = rs.getInt("atttypmod");

                if (atttypmod == 196607) {
                    type = "interval month";

                } else if (atttypmod == 327679) {
                    type = "interval year";

                } else if (atttypmod == 458751) {
                    type = "interval year to month";

                } else if (atttypmod == 589823) {
                    type = "interval day";

                } else if (atttypmod == 67174399) {
                    type = "interval hour";

                } else if (atttypmod == 67698687) {
                    type = "interval day to hour";

                } else if (atttypmod == 134283263) {
                    type = "interval minute";

                } else if (atttypmod == 201916415) {
                    type = "interval day to minute";

                } else if (atttypmod == 201392127) {
                    type = "interval hour to minute";

                } else if (atttypmod == 268500991) {
                    type = "interval second";

                } else if (atttypmod >= 268435457 && atttypmod <= 268435462) {
                    type = "interval second(p)";

                } else if (atttypmod == 402718719) {
                    type = "interval minute to second";

                } else if (atttypmod >= 402653185 && atttypmod <= 402653190) {
                    type = "interval minute to second(p)";

                } else if (atttypmod == 469827583) {
                    type = "interval hour to second";

                } else if (atttypmod >= 469762049 && atttypmod <= 469762054) {
                    type = "interval hour to second(p)";

                } else if (atttypmod == 470351871) {
                    type = "interval day to second";

                } else if (atttypmod >= 470286337 && atttypmod <= 470286342) {
                    type = "interval day to second(p)";

                } else if (atttypmod >= 2147418113 && atttypmod <= 2147418118) {
                    type = "interval(p)";

                } else {
                    type = "interval";

                }
            }

        } finally {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        }

        return type;
    }
}
